This invention is in the field of information processing, and more particularly in the field of modelling and database query generation and the disambiguation of joins.
Currently, information systems use predefined query techniques to hide the complexity of Structured Query Language (SQL) and relational databases. This allows users to specify parameters in order to add some conditions. Typically, members of Management Information System (MIS) staff build a database solution by creating user-dedicated tables, relational views or predefined SQL queries which are then made available to users by means of menus or similar techniques. In these systems, if end-users want to change the purpose of a query they ask the MIS staff to program another query. Alternatively the user may program the SQL query or command themselves. However, the syntax of non-procedural structured query language (in particular SQL) is complex, and typically, the data structure is not expressed in terms of the users' everyday work. Relational databases store information as well as metadata (data describing the data organisation) such as tables, columns, keys, indices, and their structure and design. Although suited to the overall needs of the customer organisation, these databases will likely contain much that is not of interest to a particular user. In addition, although a query may be syntactically correct, its results may not be what is expected, due to the inherent complexity of a large scale database. Indeed, the results may be totally meaningless.
For these and other reasons modeling tools are often used that allow conceptual modeling of databases in a graphical form. These tools provide a layer on top of the database, and allow the underlying database to be accessed in terms that are more relevant to a particular end application. Such modeling tools include “Impromptu”, “Transformer”, and “Architect” by Cognos Incorporated. Within such systems the join operation is used to synthesize a ‘virtual’ table from more than one real table. It is common in the design of databases, and in the queries that run against them, for there to be more than one way to join tables—this is known as join ambiguity. Because the results of a query depend on the join selected, any query must have a way to choose which of the available joins is to be used—a process often called join disambiguation.
An example of a simple join ambiguity is shown in FIG. 1. Two tables Table A 100, Table B 110 each comprising two fields, A1 101, A2 102 and B1 111, B2 112 respectively, and they are associated by two relationships or joins, Join 1 120, and Join 2 121. If a query identifies A1 101 and B1 111 then the ambiguity is that it is unclear whether Join 1 120 or Join 2 121 is the appropriate one to use.
One way of removing the join ambiguity is to require the user to specify the join explicitly. Another is to present the possibilities to the user, and require that one possibility be chosen before the query can be run. Both of these approaches create problems, because they require the user to be exposed to, and understand the detail of, data that are typically not directly related to the question that the user wants the database to answer.
Typical examples of a solution requiring the user to select from a number of options are illustrated in U.S. Pat. No. 6,247,008 “Relational database access system using semantically dynamic objects”, Cambot, et al. and U.S. Pat. No. 5,555,403 “Relational database access system using semantically dynamic objects” Cambot, et al. in which the user may choose from a list of computed contexts. In case of join ambiguity, the automatic generation of joins by these inventions is such that it generates all the elements of an SQL statement automatically, which defines all the joins and the temporary tables needed to create a correct statement. These inventions then compute a set of contexts (a consistent set of joins) or propose a suitable set of contexts to the user. A context would be given a name that is somewhat meaningful to the user. Although this approach allows users to work with their own familiar business words and terminology and to access relational databases without being required to possess any knowledge of the structure of the database or the specific names of its physical elements, it does so at the cost of having to present the user with alternatives from which to choose in order to resolve join ambiguities. Furthermore, making this choice in itself often requires a level of detailed knowledge that may not be directly relevant to the user's immediate problem.
What is needed is a way to choose between ambiguous joins that more completely transfers the burden of making this choice from the user to the specialist who creates the application.